﻿using Dapper;
using DapperExtensions;
using FreeSql;
using Ldy.Core;
using Oracle.ManagedDataAccess.Client;
using System;
using System.Collections.Generic;
using System.Data;
using System.Linq;
using System.Reflection;

namespace Ldy.DataBase
{
    /// <summary>
    /// lil 数据库功能类 {21EB22A9-1A47-4EDB-BD53-FE030755C7D8}
    /// </summary>
    public class DB
    {
        /// <summary>
        /// 数据库类型
        /// </summary>
        protected static string DataBaseType { get; set; } = Sys.GetAppSettings("DataBaseType") ?? FreeSql.DataType.Oracle.ToString();

        /// <summary>
        /// 数据库连接字符串
        /// </summary>
        protected static string ConnectionString { get; set; } = Sys.GetConnectionString("ConnectionString");

        /// <summary>
        /// 数据库连接字符串
        /// </summary>
        public static string ConnString { get; set; }

        public static readonly IFreeSql FSql = new FreeSql.FreeSqlBuilder().UseConnectionString((FreeSql.DataType)Enum.Parse(typeof(FreeSql.DataType), DataBaseType), ConnectionString).UseNameConvert(FreeSql.Internal.NameConvertType.ToUpper)
                //.UseAutoSyncStructure(true) //自动同步实体结构到数据库，FreeSql不会扫描程序集，只有CRUD时才会生成表。
                .Build();

        static DB()
        {
            Log.Write("============数据库操作开始============");
            Log.Write("【数据库类型】：" + DataBaseType);
            Log.Write("【数据库连接字符串】：" + ConnectionString);
            FSql.Aop.CurdBefore += Aop_CurdBefore;
        }

        #region Dapper
        /// <summary>
        /// 创建数据库连接
        /// </summary>
        /// <returns></returns>
        private static IDbConnection CreateConnection()
        {
            ConnString = ConnString ?? ConnectionString;
            if (string.IsNullOrEmpty(ConnString)) throw new ArgumentNullException("ConnectionString is null");
            switch (DataBaseType)
            {
                case "Oracle": return new OracleConnection(ConnString);
                case "SqlServer": return new System.Data.SqlClient.SqlConnection(ConnString);
                case "OleDb": return new System.Data.OleDb.OleDbConnection(ConnString);
                default: return new OracleConnection(ConnString);
            }
        }

        /// <summary>
        /// 查询
        /// </summary>
        /// <param name="sql"></param>
        /// <param name="param"></param>
        /// <param name="transaction"></param>
        /// <param name="buffered"></param>
        /// <param name="commandTimeout"></param>
        /// <param name="commandType"></param>
        /// <returns></returns>
        public static IList<dynamic> Query(string sql, string param = null, IDbTransaction transaction = null, bool buffered = true, int? commandTimeout = null, CommandType? commandType = null)
        {
            using (IDbConnection dbConnection = CreateConnection())
            {
                Log.Debug("【Sql】：" + sql);
                Log.Debug("【Param】：" + param?.ToString());
                return dbConnection.Query(sql, param, transaction, buffered, commandTimeout, commandType).ToList();
            }
        }

        /// <summary>
        /// 查询
        /// </summary>
        /// <param name="type"></param>
        /// <param name="sql"></param>
        /// <param name="param"></param>
        /// <param name="transaction"></param>
        /// <param name="buffered"></param>
        /// <param name="commandTimeout"></param>
        /// <param name="commandType"></param>
        /// <returns></returns>
        public static IList<object> Query(Type type, string sql, string param = null, IDbTransaction transaction = null, bool buffered = true, int? commandTimeout = null, CommandType? commandType = null)
        {
            using (IDbConnection dbConnection = CreateConnection())
            {
                Log.Debug("【Sql】：" + sql);
                Log.Debug("【Param】：" + param?.ToString());
                return dbConnection.Query(type, sql, param, transaction, buffered, commandTimeout, commandType).ToList();
            }
        }

        /// <summary>
        /// 查询
        /// </summary>
        /// <typeparam name="T"></typeparam>
        /// <param name="sql"></param>
        /// <param name="param"></param>
        /// <param name="transaction"></param>
        /// <param name="buffered"></param>
        /// <param name="commandTimeout"></param>
        /// <param name="commandType"></param>
        /// <returns></returns>
        public static IList<T> Query<T>(string sql, object param = null, IDbTransaction transaction = null, bool buffered = true, int? commandTimeout = null, CommandType? commandType = null)
        {
            //string tableName = typeof(T).GetCustomAttributes(false).OfType<TableAttribute>().FirstOrDefault().Name;
            using (IDbConnection dbConnection = CreateConnection())
            {
                Log.Debug("【Sql】：" + sql);
                Log.Debug("【Param】：" + param?.ToString());
                SqlMapper.SetTypeMap(typeof(T), new ColumnAttributeTypeMapper<T>());
                return dbConnection.Query<T>(sql, param, transaction, buffered, commandTimeout, commandType).ToList();
            }
        }

        public static IList<T> Query<T>(string field = "*", object param = null)
        {
            string tableName = typeof(T).GetCustomAttributes(false).OfType<DbTableAttribute>().FirstOrDefault().Name;
            string sql = string.Format("select {0} from {1}", field, tableName);
            using (IDbConnection dbConnection = CreateConnection())
            {
                Log.Debug("【Sql】：" + sql);
                Log.Debug("【Param】：" + param?.ToString());
                SqlMapper.SetTypeMap(typeof(T), new ColumnAttributeTypeMapper<T>());
                return dbConnection.Query<T>(sql, param, null, true, null, null).ToList();
            }
        }


        /// <summary>
        /// 查询一条
        /// </summary>
        /// <param name="sql"></param>
        /// <param name="param"></param>
        /// <param name="transaction"></param>
        /// <param name="commandTimeout"></param>
        /// <param name="commandType"></param>
        /// <returns></returns>
        public static dynamic QueryFirstOrDefault(string sql, object param = null, IDbTransaction transaction = null, int? commandTimeout = null, CommandType? commandType = null)
        {
            using (IDbConnection dbConnection = CreateConnection())
            {
                Log.Debug("【Sql】：" + sql);
                Log.Debug("【Param】：" + param?.ToString());
                return dbConnection.QueryFirstOrDefault(sql, param, transaction, commandTimeout, commandType);
            }
        }

        /// <summary>
        /// 查询一条
        /// </summary>
        /// <typeparam name="T"></typeparam>
        /// <param name="sql"></param>
        /// <param name="param"></param>
        /// <param name="transaction"></param>
        /// <param name="commandTimeout"></param>
        /// <param name="commandType"></param>
        /// <returns></returns>
        public static T QueryFirstOrDefault<T>(string sql, object param = null, IDbTransaction transaction = null, int? commandTimeout = null, CommandType? commandType = null)
        {
            using (IDbConnection dbConnection = CreateConnection())
            {
                Log.Debug("【Sql】：" + sql);
                Log.Debug("【Param】：" + param?.ToString());
                return dbConnection.QueryFirstOrDefault<T>(sql, param, transaction, commandTimeout, commandType);
            }
        }

        /// <summary>
        /// 执行
        /// </summary>
        /// <param name="sql"></param>
        /// <param name="param"></param>
        /// <param name="transaction"></param>
        /// <param name="commandTimeout"></param>
        /// <param name="commandType"></param>
        /// <returns></returns>
        public static int Execute(string sql, object param = null, IDbTransaction transaction = null, int? commandTimeout = null, CommandType? commandType = null)
        {
            using (IDbConnection dbConnection = CreateConnection())
            {
                Log.Debug("【Sql】：" + sql);
                Log.Debug("【Param】：" + param?.ToString());
                return dbConnection.Execute(sql, param, transaction, commandTimeout, commandType);
            }
        }

        /// <summary>
        /// 插入
        /// </summary>
        /// <typeparam name="T"></typeparam>
        /// <param name="entity"></param>
        /// <param name="transaction"></param>
        /// <param name="commandTimeout"></param>
        /// <returns></returns>
        public static dynamic Insert<T>(T entity, IDbTransaction transaction = null, int? commandTimeout = null) where T : class
        {
            using (IDbConnection dbConnection = CreateConnection())
            {
                return dbConnection.Insert<T>(entity, transaction, commandTimeout);
            }
        }

        /// <summary>
        /// 插入
        /// </summary>
        /// <typeparam name="T"></typeparam>
        /// <param name="entities"></param>
        /// <param name="transaction"></param>
        /// <param name="commandTimeout"></param>
        public static void Insert<T>(IEnumerable<T> entities, IDbTransaction transaction, int? commandTimeout) where T : class
        {
            using (IDbConnection dbConnection = CreateConnection())
            {
                dbConnection.Insert<T>(entities, transaction, commandTimeout);
            }
        }

        /// <summary>
        /// 更新
        /// </summary>
        /// <typeparam name="T"></typeparam>
        /// <param name="entity"></param>
        /// <param name="transaction"></param>
        /// <param name="commandTimeout"></param>
        /// <param name="ignoreAllKeyProperties"></param>
        /// <returns></returns>
        public static bool Update<T>(T entity, IDbTransaction transaction = null, int? commandTimeout = null, bool ignoreAllKeyProperties = false) where T : class
        {
            using (IDbConnection dbConnection = CreateConnection())
            {
                return dbConnection.Update<T>(entity, transaction, commandTimeout, ignoreAllKeyProperties);
            }
        }

        /// <summary>
        /// 更新
        /// </summary>
        /// <typeparam name="T"></typeparam>
        /// <param name="entity"></param>
        /// <returns></returns>
        public static int InsertOrUpdate<T>(T entity) where T : class
        {
            using (IDbConnection dbConnection = CreateConnection())
            {
                return dbConnection.InsertOrUpdate<T>().SetSource(entity).ExecuteAffrows();
            }
        }

        /// <summary>
        /// 删除
        /// </summary>
        /// <typeparam name="T"></typeparam>
        /// <param name="entity"></param>
        /// <param name="transaction"></param>
        /// <param name="commandTimeout"></param>
        /// <returns></returns>
        public static bool Delete<T>(T entity, IDbTransaction transaction = null, int? commandTimeout = null) where T : class
        {
            using (IDbConnection dbConnection = CreateConnection())
            {
                return dbConnection.Delete<T>(entity, transaction, commandTimeout);
            }
        }


        public static void Transaction(Action<IDbConnection> action = null)
        {
            using (IDbConnection conn = CreateConnection())
            {
                conn.Open();
                IDbTransaction tran = conn.BeginTransaction();
                try
                {
                    action?.Invoke(conn);
                    tran.Commit();
                }
                catch(Exception ex)
                {
                    Log.Error("执行数据事务异常，错误信息：" + ex.Message);
                    tran.Rollback();
                }
            }
        }

        #endregion

        #region FreeSql
        /// <summary>
        /// 数据库SQL日志
        /// </summary>
        /// <param name="sender"></param>
        /// <param name="e"></param>
        private static void Aop_CurdBefore(object sender, FreeSql.Aop.CurdBeforeEventArgs e)
        {
            Log.Debug("【Table】：" + e.Table.DbName);
            Log.Debug("【Sql】：" + e.Sql);
            Log.Debug("【Param】：" + string.Join(",",e.DbParms.ToList().Select(d=> "[" + d.ParameterName + "]:[" + d.Value + "]").ToArray()));
        }

        #endregion

    }
}
